package com.zzwtel.autocode.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import com.zaxxer.hikari.HikariDataSource;
import com.zzwtel.autocode.template.constants.ModelPackage;
import com.zzwtel.autocode.template.constants.ViewType;
import com.zzwtel.autocode.template.model.Property;
import com.zzwtel.autocode.template.model.Table;
import com.zzwtel.autocode.util.HumpUtil;

public class DBUtil {
	
	public static List<Table> getAllTables(){
		Connection con = null;
		ResultSet rs = null;
		try{
			List<Table> tables = new ArrayList<Table>();			 
			HikariDataSource ds = DataSourceUtil.getDataSource();
			con = ds.getConnection();
			DatabaseMetaData dbmd = con.getMetaData();			
			rs = dbmd.getTables(con.getCatalog(), "root",null,new String[]{"TABLE"}); 
			while(rs.next()){				
				Table table = new Table();
				String tableName = rs.getString("TABLE_NAME");
				table.setName(tableName);
				String model = HumpUtil.camelName(tableName);
				table.setModel(model);				
				table.setClazz(ModelPackage.packageName+"."+model);
				tables.add(table);
				//System.out.println("tableName:"+tableName);
			}
			//con.close();
			return tables;
		}catch(Exception e){
			throw new RuntimeException(e);
		}finally{
			close(con,rs);
		}		 
		
	}
	
	
	/**
	 * 根据table获取属性列表
	 * @param table
	 * @return
	 */
	public static List<Property> getProps(Table table){
		return getProps(table.getName());
	}
	/**
	 * 根据表名获取字段列表
	 * @return
	 */
	public static List<Property> getProps(String tableName){
		List<Property> list = new ArrayList<Property>();		
		HikariDataSource ds = DataSourceUtil.getDataSource();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			con = ds.getConnection();
			String sql = "select * from "+tableName;
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData data = rs.getMetaData();
			
			DatabaseMetaData meta = con.getMetaData();
			ResultSet ids = meta.getPrimaryKeys("", "", tableName);
			String key = "id";
			/*
			while(ids.next()){				
				key = ids.getString("COLUMN_NAME");				
			}*/
			//目前只支持单主键，都主键不支持
			ids.next();
			key = ids.getString("COLUMN_NAME");	
			
			for(int i=1;i<=data.getColumnCount();i++){
				// 获得指定列的列名
				String columnName = data.getColumnName(i);
				// 获得指定列的数据类型名
				String columnTypeName = data.getColumnTypeName(i);
				// 对应数据类型的类
				String columnClassName = data.getColumnClassName(i);				
				String propertyName = HumpUtil.camelName(columnName);					
			
				Property property = new Property();
				property.setColumn(columnName);
				if(key.equals(columnName)){
					property.setKey(true);
				}else{
					property.setKey(false);
				}				
				property.setName(propertyName);
				//默认设置为普通文本框
				property.setViewType(ViewType.TEXT);		
				list.add(property);
				//System.out.println("columnName:"+columnName+" columnTypeName:"+columnTypeName+" columnClassName:"+columnClassName);
			}
			
		}catch(Exception e){
			throw new RuntimeException(e);
		}finally{
			close(con,stmt,rs);
		}	
		return list;
	}
	
	
	
	/**
	 * 释放连接
	 * @param con 
	 */
	public static void close(Connection con){
		close(con,null,null);
	}
	
	/**
	 * 释放连接	
	 * @param stmt	
	 */
	public static void close(PreparedStatement stmt){
		close(null,stmt,null);
	}
	
	/**
	 * 释放连接	
	 * @param rs	
	 */
	public static void close(ResultSet rs){
		close(null,null,rs);
	}
	

	/**
	 * 释放连接
	 * @param con
	 * @param rs
	 */
	public static void close(Connection con,ResultSet rs){
		close(con,null,rs);
	}
	
	
	/**
	 * 释放连接
	 * @param con
	 * @param stmt
	 * @param rs
	 */
	public static void close(Connection con,PreparedStatement stmt,ResultSet rs){
		try{
			if(rs != null){
				rs.close();
			}
			if(stmt != null){
				stmt.close();
			}
			if(con != null){
				con.close();
			}
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}
	
	
	
	public static void main(String[] args){		
		List<Table> list = getAllTables();
		for(Table t : list){
			System.out.println("name:"+t.getName()+" model:"+t.getModel()+" clazz:"+t.getClazz());
		}
		getProps("cell");
	}
	
	
}
